// 加载express模块
const express=require('express');
//加载mysql模块
const mysql=require('mysql');
//加载bosdyParser模块
const bodyParser=require('body-parser');
//加载MD5模块
const md5=require('md5');
//创建MySQL连接池
const pool=mysql.createPool({
    host:'127.0.0.1',   //MySQL服务器地址
    port:'3306',        //MySQL服务器端口号
    user:'root',        //数据库用户的用户名
    password:'',        //数据库用户密码
    database:'xzqa',    //数据库名称
    connectionLimit:20, //最大连接数
    charset:'utf8'    //数据库服务器的编码方式
});
//创建服务器对象
const server=express();

server.use(bodyParser.urlencoded({
    extended:false
}))
//加载CORS模块
const cors=require('cors');
const { Console } = require('console');
//使用CORS中间价
server.use(cors({
    origin:['http://localhost:8081','http://127.0.0.1:8081']
}));
//获取所有文章所有分类的接口
server.get('/category',(req,res)=>{
    //sql语句以获取文章分类表的数据
    let sql='select id,category_name from xzqa_category order by id';
    //执行SQL语句
    pool.query(sql,(error,results)=>{
        if(error) throw error;
        res.send({message:'ok',code:200,results:results});
    });
});

//获取指定分类下包含文章数据的接口
server.get('/articles',(req,res)=>{
    //获取客户端传递的cid参数
    let cid=req.query.cid;
     //获取客户端传递的page参数
     let page=req.query.page;
     //储存每页显示的记录数
     let pagesize=20;
     //通过公式计算从第几条记录开始返回
     let offset=(page-1)*pagesize;
     //用于储存获取到的总计录数
     let rowcount;
     //获取指定分类下的文章总数
     let sql='select count(id) as count from xzqa_article where category_id=?';
     pool.query(sql,[cid],(error,results)=>{
         if(error) throw error;
         //将获取到的总记录数赋给rowcount变量
         rowcount=results[0].count;
          //根据总记录数和每页显示的记录数来计算总数
            let pagecount=Math.ceil(rowcount/pagesize);
            //查询SQL语句
            //查询所有
            // let sql='select id,subject,description,image from xzqa_article where category_id=?';
            // 查询部分
            sql='select id,subject,description,image from xzqa_article where category_id=? limit ?,?';
            //执行SQL语句
            pool.query(sql,[cid,offset,pagesize],(error,results)=>{
                if(error) throw error;
                res.send({message:'ok',code:200,results:results,pagecount:pagecount});
            });
        });
    
});
//获取特定文章数据的接口
server.get('/detail',(req,res)=>{
    //获取地址栏中的id参数
    let id=req.query.id;
    // SQL查询
    let sql=" select r.id,subject,content,created_at,nickname,avatar,article_number from xzqa_article as r inner join xzqa_author as u on author_id=u.id where r.id=?";
    //执行SQL查询
    pool.query(sql,[id],(error,results)=>{
        if(error) throw error;
         //返回数据到客户端
        res.send({message:'ok',code:200,result:results[0]});
    })
})
//用户注册接口
server.post('/register',(req,res)=>{
    // 获取用户名和密码信息
    // console.log(md5('12345678'));
    let username=req.body.username;
    let password=req.body.password;
    //以username为条件进行查找操作，以保证用户名的唯一性
    let sql='select count(id) as count from xzqa_author where username=?';
    pool.query(sql,[username],(error,results)=>{
        if(error) throw error;
        let count=results[0].count;
        if(count==0){
            //将用户的相关信息插入到数据表
            sql='insert xzqa_author(username,password) values(?,MD5(?))';
            pool.query(sql,[username,password],(error,results)=>{
                if(error) throw error;
                res.send({message:'ok',code:200});
            })
        }else{
            res.send({message:'user exists',code:201});
        }
    })
});
//用户登录接口
server.post('/login',(req,res)=>{
    //获取用户名和密码信息
    let username=req.body.username;
    let password=req.body.password;
    //SQL语句
    sql='select id,username,nickname,avatar from xzqa_author where username=? and password=MD5(?)';
    pool.query(sql,[username,password],(error,results)=>{
        if(error) throw error;
        if(results.length==0){//登录失败
            res.send({message:'login failed',code:201});
        }else{//登录成功
            res.send({message:'ok',code:200,result:results[0]});
        }
    })
})
//指定服务器对象监听的端口号
server.listen(3000,()=>{
    console.log('server is running...')
    
})